package com.dreamcode.anfeedreader.database;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

public class DbHelper {

	public static final String DEVICE_ALERT_ENABLED_ZIP = "DAEZ99";
	public static final String DB_NAME = "anfeedreader.db";
	public static final String DB_TABLE_FEED = "Feed";
	public static final String DB_TABLE_FEEDITEM = "FeedItem";
	public static final String DB_TABLE_CATEGORIES = "Categories";
	public static final String DB_TABLE_CONTACTS = "Contacts";
	public static final String DB_TABLE_ACCOUNTS = "AccountSettings";
	public static final String DB_TABLE_SEARCHFEEDITEM = "SearchTable";
	public static final int DB_VERSION = 3;
	private static final String CLASSNAME = DbHelper.class.getSimpleName();
	private static String TAG = "DBHelper";
	private static DbHelper dbHelper = null;
	private SQLiteDatabase db = null;

	public SQLiteDatabase getDb() {
		return db;
	}

	public void setDb(SQLiteDatabase db) {
		this.db = db;
	}

	private final DBOpenHelper dbOpenHelper;

	public DbHelper(Context context) {
		this.dbOpenHelper = new DBOpenHelper(context, "WR_DATA", null, 1);
		this.establishDB();
	}
	
	public static DbHelper getInstance(Context context) {
		if (dbHelper == null) {
			dbHelper = new DbHelper(context);
		}
		return dbHelper;
	}
	

	private void establishDB() {
		if (this.db == null) {
			this.db = this.dbOpenHelper.getWritableDatabase();
		}
	}

	public void close(SQLiteDatabase db) {
		try {
			db.close();
		} catch (SQLException e) {
			Log.d(TAG, "close exception: " + e.getLocalizedMessage());
		}
	}

	public static class Location {
		public long id;
		public String url;
		public String title;
		public String description;
	}

	private static class DBOpenHelper extends SQLiteOpenHelper {

		private static final String CREATE_TABLE_FEED = " create table "
				+ DbHelper.DB_TABLE_FEED
				+ " (_id integer primary key autoincrement,"
				+ " feedname varchar, feedurl varchar not null, favicon blob, category integer);";

		private static final String CREATE_TABLE_FEEDITEMS = " create table "
				+ DbHelper.DB_TABLE_FEEDITEM
				+ " (_id integer primary key autoincrement,"
				+ " feed_id integer, title varchar not null, url varchar not null,pubdate varchar, body text, md5hash varchar, status varchar);";

		private static final String CREATE_TABLE_CATEGORIES = " create table "
				+ DbHelper.DB_TABLE_CATEGORIES
				+ " (_id integer primary key autoincrement,"
				+ " category varchar not null);";
		
		private static final String CREATE_TABLE_ACCOUNTS = " create table "
			+ DbHelper.DB_TABLE_ACCOUNTS
			+ " (_id integer primary key autoincrement,"
			+ " type varchar not null, accesstoken varchar, tokensecret varchar);";

		private static final String CREATE_TABLE_CONTACTS = " create table "
				+ DbHelper.DB_TABLE_CONTACTS
				+ " (_id integer primary key autoincrement,"
				+ " contactname varchar not null, channel varchar, uuId varchar);";

		private static final String CREATE_VIRTUAL_TABLE = "CREATE VIRTUAL TABLE "
				+ DB_TABLE_SEARCHFEEDITEM
				+ " USING FTS3 (md5hash varchar,"
				+ "title varchar, body text);";

		public DBOpenHelper(Context context, String name,
				CursorFactory factory, int version) {
			super(context, DbHelper.DB_NAME, null, DbHelper.DB_VERSION);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			try {
				db.execSQL(DBOpenHelper.CREATE_TABLE_FEED);
				db.execSQL(DBOpenHelper.CREATE_TABLE_FEEDITEMS);
				db.execSQL(DBOpenHelper.CREATE_TABLE_CONTACTS);
				db.execSQL(DBOpenHelper.CREATE_TABLE_CATEGORIES);
				db.execSQL(DBOpenHelper.CREATE_TABLE_ACCOUNTS);
				db.execSQL(DBOpenHelper.CREATE_VIRTUAL_TABLE);
			} catch (SQLException e) {
				Log.e("", DbHelper.CLASSNAME, e);
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			db.execSQL("DROP TABLE IF EXISTS" + DbHelper.DB_TABLE_FEEDITEM);
			this.onCreate(db);
			db.execSQL("DROP TABLE IF EXISTS" + DbHelper.DB_TABLE_FEED);
			this.onCreate(db);
			db.execSQL("DROP TABLE IF EXISTS" + DbHelper.DB_TABLE_CATEGORIES);
			this.onCreate(db);
		}

		@Override
		public void onOpen(SQLiteDatabase db) {
			super.onOpen(db);
		}

	}
}
